﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;

/// <summary>
/// Summary description for Communication
/// </summary>
public class Communication
{
	public Communication()
	{
        System.Data.SqlClient.SqlConnection.ClearAllPools();
	}

    SqlCommand cm = null;
    SqlDataReader rd = null;
    DataSet ds = null;
    SqlDataAdapter da = null;
    MD5 md5 = null;

    public static SqlConnection ConnectionOpen()
    {
        SqlConnection cn = null;
        cn = new SqlConnection();
        cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringDev"].ConnectionString;
        cn.Open();
        return cn;
    }

    #region private variables

    private string _fName = string.Empty;
    private string _lName = string.Empty;
    private string _dob = string.Empty;
    private string _email = string.Empty;
    private string _pwd = string.Empty;
    private string _salt = string.Empty;
    private string _usrname = string.Empty;
    private int _userId = 0;
    private int _usertypeid = 0;
    private int _groupid = 0;
    private string _add = string.Empty;
    int _ispaid = 0;

    #endregion

    #region public properties

    public string FirstName
    {
        get { return _fName; }
        set { _fName = value; }
    }
    public string LastName
    {
        get { return _lName; }
        set { _lName = value; }
    }
    public string EMail
    {
        get { return _email; }
        set { _email = value; }
    }
    public string UserName { set { _usrname = value; } get { return _usrname; } }
    public int UserID
    {
        get { return _userId; }
        set { _userId = value; }
    }
    public int UserTypeID { set { _usertypeid = value; } get { return _usertypeid; } }
    public int IsPaid { set { _ispaid = value; } get { return _ispaid; } }


    public int GroupID { set { _groupid = value; } get { return _groupid; } }

    int _msgid = 0;
    public int MessageID { set { _msgid = value; } get { return _msgid; } }

    int _senderid = 0;
    public int SenderID { set { _senderid = value; } get { return _senderid; } }

    int _receiver = 0;
    public int ReceiverID { set { _receiver = value; } get { return _receiver; } }

    int _iscc = 0;
    public int IsCC { set { _iscc = value; } get { return _iscc; } }

    int _hasattach = 0;
    public int HasAttachment { set { _hasattach = value; } get { return _hasattach; } }

    int _isread = 0;
    public int IsRead { set { _isread = value; } get { return _isread; } }
    
    string _msg = string.Empty;
    public string Body { set { _msg = value; } get { return _msg; } }

    //string _grpkey = string.Empty;
    //public string GroupKey { set { _grpkey = value; } get { return _grpkey; } }

    string _subject = string.Empty;
    public string Subject { set { _subject = value; } get { return _subject; } }

    string _msgtype= string.Empty;
    public string MessageType { set { _msgtype = value; } get { return _msgtype; } }

    int _commid = 0;
    public int CommunicationID { set { _commid = value; } get { return _commid; } }

    private string _silpath = string.Empty;
    public string SilPath { set { _silpath = value; } get { return _silpath; } }
    
    #endregion

    public DataSet Select_Messages()
    {
        DataSet ds = new DataSet();
        using (SqlCommand cmd = new SqlCommand("ALM.Select_Messages"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spUserID = new SqlParameter("@UserID", SqlDbType.Int);
                spUserID.Value = this.UserID;
                cmd.Parameters.Add(spUserID);

                SqlParameter spMessageID = new SqlParameter("@MessageID", SqlDbType.Int);
                spMessageID.Value = this.MessageID;
                cmd.Parameters.Add(spMessageID);


                SqlParameter spMessageType = new SqlParameter("@MessageType", SqlDbType.VarChar);
                spMessageType.Value = this.MessageType;
                cmd.Parameters.Add(spMessageType);

                using (da = new SqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                }
            }
        }
        return ds;
    }

    public int Delete_SentMessage()
    {
        int Result = 0;
        using (SqlCommand cmd = new SqlCommand("ALM.Delete_SentMessage"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spUserID = new SqlParameter("@UserID", SqlDbType.Int);
                spUserID.Value = this.UserID;
                cmd.Parameters.Add(spUserID);

                SqlParameter spMessageID = new SqlParameter("@MessageID", SqlDbType.Int);
                spMessageID.Value = this.MessageID;
                cmd.Parameters.Add(spMessageID);

                Result = cmd.ExecuteNonQuery();
            }
        }
        return Result;
    }

    public int Delete_InboxMessage()
    {
        int Result = 0;
        using (SqlCommand cmd = new SqlCommand("ALM.Delete_InboxMessage"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spUserID = new SqlParameter("@UserID", SqlDbType.Int);
                spUserID.Value = this.UserID;
                cmd.Parameters.Add(spUserID);

                SqlParameter spCommunicationID = new SqlParameter("@CommunicationID", SqlDbType.Int);
                spCommunicationID.Value = this.CommunicationID;
                cmd.Parameters.Add(spCommunicationID);

                Result = cmd.ExecuteNonQuery();
            }
        }
        return Result;
    }

    //public int Delete_MessageSender()
    //{
    //    int Result = 0;
    //    using (SqlCommand cmd = new SqlCommand("ALM.Delete_MessageSender"))
    //    {
    //        using (SqlConnection cn = ConnectionOpen())
    //        {
    //            cmd.Connection = cn;
    //            cmd.CommandType = CommandType.StoredProcedure;
    //            cmd.CommandTimeout = 0;
                 

    //            SqlParameter spMessageID = new SqlParameter("@MessageID", SqlDbType.Int);
    //            spMessageID.Value = this.MessageID;
    //            cmd.Parameters.Add(spMessageID);

    //            Result = cmd.ExecuteNonQuery();
    //        }
    //    }
    //    return Result;
    //}

    public int MarkAsRead_Message()
    {
        int Result = 0;
        using (SqlCommand cmd = new SqlCommand("ALM.MarkAsRead"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spUserID = new SqlParameter("@UserID", SqlDbType.Int);
                spUserID.Value = this.UserID;
                cmd.Parameters.Add(spUserID);

                SqlParameter spCommunicationID = new SqlParameter("@CommunicationID", SqlDbType.Int);
                spCommunicationID.Value = this.CommunicationID;
                cmd.Parameters.Add(spCommunicationID);

                Result = cmd.ExecuteNonQuery();
            }
        }
        return Result;
    }


    //public bool SaveUploadedDocuments(int FileLength, string FileName, HttpPostedFile FileData,string FileType)
    //{
    //    bool UploadedDocument = false;
    //    int nFileLen = 0, Result=0;
    //    byte[] FileBuffer = null;

    //    if (FileLength > 0)
    //    {
    //        nFileLen = FileData.ContentLength;
    //        System.IO.Stream objStream = FileData.InputStream;
    //        FileBuffer = new byte[nFileLen];
    //        objStream.Read(FileBuffer, 0, nFileLen);
    //    }

    //    using (cm = new SqlCommand("ALM.SaveUploadedDocument"))
    //    {
    //        using (ds = new DataSet())
    //        {
    //            using (SqlConnection cn = ConnectionOpen())
    //            {
    //                cm.CommandType = CommandType.StoredProcedure;
    //                cm.Connection = cn;

    //                SqlParameter sMessageID = new SqlParameter("@MessageID", SqlDbType.Int);
    //                SqlParameter sFileName = new SqlParameter("@FileName", SqlDbType.VarChar);
    //                SqlParameter sFileType = new SqlParameter("@FileType", SqlDbType.VarChar);
    //                SqlParameter sFileLength = new SqlParameter("@FileLength", SqlDbType.BigInt);
    //                SqlParameter sFileData = new SqlParameter("@FileData", SqlDbType.Image);
    //                sMessageID.Value = this.MessageID;

    //                if ( FileLength > 0)
    //                {
    //                    if ( FileName != "")
    //                    {
    //                        sFileData.Value = FileBuffer;
    //                        sFileName.Value = FileName;
    //                        sFileType.Value =  FileType;
    //                        sFileLength.Value = nFileLen;
    //                    }
    //                }
    //                else
    //                {
    //                    sFileData.Value = DBNull.Value;
    //                    sFileName.Value = DBNull.Value;
    //                    sFileType.Value = DBNull.Value;
    //                    sFileLength.Value = DBNull.Value;
    //                }

    //                cm.Parameters.Add(sMessageID);
    //                cm.Parameters.Add(sFileData);
    //                cm.Parameters.Add(sFileName);
    //                cm.Parameters.Add(sFileType);
    //                cm.Parameters.Add(sFileLength);

    //                Result = cm.ExecuteNonQuery();
    //                if (Result > 0)
    //                {
    //                    UploadedDocument = true;
    //                }
    //                cn.Close();
    //            }
    //        }
    //    }
    //    return UploadedDocument;
    //}

    public int SendMessages()
    {
        int Result = 0;
        using (SqlCommand cmd = new SqlCommand("ALM.SendMessage"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spSenderID = new SqlParameter("@SenderID", SqlDbType.Int);
                spSenderID.Value = this.SenderID;
                cmd.Parameters.Add(spSenderID);

                SqlParameter spReceiverID = new SqlParameter("@ReceiverID", SqlDbType.Int);
                spReceiverID.Value = this.ReceiverID;
                cmd.Parameters.Add(spReceiverID);

                SqlParameter spSubject = new SqlParameter("@Subject", SqlDbType.VarChar);
                spSubject.Value = this.Subject;
                cmd.Parameters.Add(spSubject);

                SqlParameter spBody = new SqlParameter("@Body", SqlDbType.VarChar);
                spBody.Value = this.Body;
                cmd.Parameters.Add(spBody);

                SqlParameter spIsCC = new SqlParameter("@IsCC", SqlDbType.Int);
                spIsCC.Value = this.IsCC;
                cmd.Parameters.Add(spIsCC);

                SqlParameter spHasAttachment = new SqlParameter("@HasAttachment", SqlDbType.Int);
                spHasAttachment.Value = this.HasAttachment;
                cmd.Parameters.Add(spHasAttachment);

                SqlParameter spMessageID = new SqlParameter("@MessageID", SqlDbType.Int);
                spMessageID.Value = this.MessageID;
                cmd.Parameters.Add(spMessageID);
                 
                //SqlParameter spGroupKey = new SqlParameter("@GroupKey", SqlDbType.VarChar);
                //spGroupKey.Value = this.GroupKey;
                //cmd.Parameters.Add(spGroupKey);

                rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    if (rd.Read())
                    {
                        if (rd["MessageID"] != DBNull.Value)
                        {
                            Result = Convert.ToInt32(rd["MessageID"]);
                        }
                    }
                }

                rd.Close();
            }
        }
        return Result;
    }

    public DataSet Select_UserList()
    {
        DataSet ds = new DataSet();
        using (SqlCommand cmd = new SqlCommand("ALM.Select_UserList"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spUserID = new SqlParameter("@UserID", SqlDbType.Int);
                spUserID.Value = this.UserID;
                cmd.Parameters.Add(spUserID); 

                using (da = new SqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                }
            }
        }
        return ds;
    }

    public int SaveAttachment_Path(string Path,string FileName)
    {
        int Result = 0;
        using (SqlCommand cmd = new SqlCommand("ALM.SaveAttachment_Path"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0; 
                 

                SqlParameter spMessageID = new SqlParameter("@MessageID", SqlDbType.Int);
                spMessageID.Value = this.MessageID;
                cmd.Parameters.Add(spMessageID);


                SqlParameter spPath = new SqlParameter("@Path", SqlDbType.VarChar);
                spPath.Value = Path;
                cmd.Parameters.Add(spPath);

                SqlParameter spFileName = new SqlParameter("@FileName", SqlDbType.VarChar);
                spFileName.Value = FileName;
                cmd.Parameters.Add(spFileName);

                Result = cmd.ExecuteNonQuery();
            }
        }
        return Result;
    }

    public int Select_PreviousMessage(int mBox, out int commId, string boxType)
    {
        commId = 0;
        int Result = 0;
        using (SqlCommand cmd = new SqlCommand("ALM.Select_PreviousMessage"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spOldMessageID = new SqlParameter("@OldMessageID", SqlDbType.Int);
                spOldMessageID.Value = this.MessageID;
                cmd.Parameters.Add(spOldMessageID);

                SqlParameter spUserID = new SqlParameter("@UserID", SqlDbType.Int);
                spUserID.Value = this.UserID;
                cmd.Parameters.Add(spUserID);

                SqlParameter spmBox = new SqlParameter("@mBox", SqlDbType.Int);
                spmBox.Value = mBox;
                cmd.Parameters.Add(spmBox);

                SqlParameter spType = new SqlParameter("@type", SqlDbType.VarChar);
                spType.Value = boxType;
                cmd.Parameters.Add(spType); 

                rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    if (rd.Read())
                    {
                        if (rd["MessageID"] != DBNull.Value)
                        {
                            Result = Convert.ToInt32(rd["MessageID"]);
                        }
                        if (rd["CommunicationID"] != DBNull.Value)
                        {
                            commId = Convert.ToInt32(rd["CommunicationID"]);
                        }
                    }
                }

                rd.Close();
            }
        }
        return Result;
    }

    public int Select_NextMessage(int mBox, out int commId, string boxType)
    {
        int Result = 0;
        commId = 0;
        using (SqlCommand cmd = new SqlCommand("ALM.Select_NextMessage"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spOldMessageID = new SqlParameter("@OldMessageID", SqlDbType.Int);
                spOldMessageID.Value = this.MessageID;
                cmd.Parameters.Add(spOldMessageID);

                SqlParameter spUserID = new SqlParameter("@UserID", SqlDbType.Int);
                spUserID.Value = this.UserID;
                cmd.Parameters.Add(spUserID);

                SqlParameter spmBox = new SqlParameter("@mBox", SqlDbType.Int);
                spmBox.Value = mBox;
                cmd.Parameters.Add(spmBox);

                SqlParameter spType = new SqlParameter("@type", SqlDbType.VarChar);
                spType.Value = boxType;
                cmd.Parameters.Add(spType); 

                rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    if (rd.Read())
                    {
                        if (rd["MessageID"] != DBNull.Value)
                        {
                            Result = Convert.ToInt32(rd["MessageID"]);
                        }
                        if (rd["CommunicationID"] != DBNull.Value)
                        {
                            commId = Convert.ToInt32(rd["CommunicationID"]);
                        }
                    }
                }

                rd.Close();
            }
        }
        return Result;
    }

    public int Count_UnreadMessage()
    {
        int Result = 0;
        using (SqlCommand cmd = new SqlCommand("ALM.Count_UnreadMessage"))
        {
            using (SqlConnection cn = ConnectionOpen())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                SqlParameter spUserID = new SqlParameter("@UserID", SqlDbType.Int);
                spUserID.Value = this.UserID;
                cmd.Parameters.Add(spUserID);

                rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    if (rd.Read())
                    {
                        if (rd["TotalUnreadMessage"] != DBNull.Value)
                        {
                            Result = Convert.ToInt32(rd["TotalUnreadMessage"]);
                        }
                    }
                }

                rd.Close();
            }
        }
        return Result;
    }

}